﻿CREATE TABLE [dbo].[Region] (
    [Id] [int] NOT NULL IDENTITY (1, 1),
    [Name] [nvarchar](1000) NOT NULL,
    [DisplayOrder] [int] NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
)
CREATE NONCLUSTERED INDEX [IX_Region_DisplayOrder]
    ON [dbo].[Region]([DisplayOrder] ASC);

INSERT INTO [dbo].[Region] VALUES ('Vancouver', 0)

ALTER TABLE [dbo].[Customer] ADD Region_Id [int]
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT Customer_Region FOREIGN KEY ([Region_Id]) REFERENCES [Region]

CREATE TABLE [dbo].[LogisticsDistrict] (
    [Id] [int] NOT NULL IDENTITY (1, 1),
    [RegionId] [int] NOT NULL,
    [StateProvinceId] [int] NOT NULL,
    [Name] [nvarchar](1000) NOT NULL,
    [DisplayOrder] [int] NOT NULL,
    [CountryCode] [nvarchar](5) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
	CONSTRAINT [LogisticsDistrict_Region] FOREIGN KEY ([RegionId]) REFERENCES [dbo].[Region] ([Id]) ON DELETE CASCADE,
	CONSTRAINT [LogisticsDistrict_StateProvince] FOREIGN KEY ([StateProvinceId]) REFERENCES [dbo].[StateProvince] ([Id]) 
)

GO
CREATE NONCLUSTERED INDEX [IX_LogisticsDistrict_DisplayOrder]
    ON [dbo].[LogisticsDistrict]([DisplayOrder] ASC);
CREATE NONCLUSTERED INDEX [IX_LogisticsDistrict_CountryCode]
    ON [dbo].[LogisticsDistrict]([CountryCode] ASC);
CREATE NONCLUSTERED INDEX [IX_LogisticsDistrict_RegionId]
    ON [dbo].[LogisticsDistrict]([RegionId] ASC);
CREATE NONCLUSTERED INDEX [IX_LogisticsDistrict_StateProvinceId]
    ON [dbo].[LogisticsDistrict]([StateProvinceId] ASC);

GO
INSERT INTO [dbo].[LogisticsDistrict] VALUES (1, 2, 'Vancouver', 0, 'CA')

GO
CREATE TABLE [dbo].[LogisticsZoneGroup] (
    [Id] [int] NOT NULL IDENTITY (1, 1),
    [Name] [nvarchar](1000) NOT NULL,
    [DistrictId] [int],
	PRIMARY KEY CLUSTERED ([Id] ASC),
	CONSTRAINT [LogisticsZoneGroup_District] FOREIGN KEY ([DistrictId]) REFERENCES [dbo].[LogisticsDistrict] ([Id])
)
CREATE NONCLUSTERED INDEX [IX_LogisticsZoneGroup_DistrictId]
    ON [dbo].[LogisticsZoneGroup]([DistrictId] ASC);

GO
ALTER TABLE [dbo].[LogisticsZone] DROP CONSTRAINT [StateProvinceId]
GO
ALTER TABLE [dbo].[LogisticsZone] DROP COLUMN [StateProvinceId], [CountryCode], [DisplayOrder]
GO


ALTER TABLE [dbo].[LogisticsZone] ADD [DistrictId] [int] NOT NULL DEFAULT '1', [ZoneGroupId] [int]
ALTER TABLE [dbo].[LogisticsZone] ADD CONSTRAINT LogisticsZone_District FOREIGN KEY ([DistrictId]) REFERENCES [LogisticsDistrict] ([Id]) ON DELETE CASCADE
ALTER TABLE [dbo].[LogisticsZone] ADD CONSTRAINT LogisticsZone_Group FOREIGN KEY ([ZoneGroupId]) REFERENCES [LogisticsZoneGroup] ([Id])


CREATE INDEX [IX_ZoneGroupId] ON [dbo].[LogisticsZone]([ZoneGroupId])
CREATE INDEX [IX_DistrictId] ON [dbo].[LogisticsZone]([DistrictId])

GO